How to Create Auto-Backup File or Data in Access
In this How To, I will show how to automatically create a backup database. I already posted the How to Create Simple Auto-Backup database here. I will show the advanced method of how to back up database in this post. We can set it to back up the file when the database file is opened or closed.
Method of Backup
- Backup database file (copy file)
- Backup table only (copy data/table only)
Related Video:
Create Table for Setting Backup Data
The table is needed to save a data for a setting backup. The users can create the backup on the way they want. The users can back up on certain day, with specific file name, override a backup file. This information will be saved in the table. So the table is needed. For this example, I want to be able to save the backup file on specific folder, specific file name, on certain day, with option of overriding the old file. I have the fields for the backup table listed below.
Data Sheet View of Backup Table:
Create Form for Setting Backup
Need to create the form for setting backup information and drag all fields to this form. For example below, I add a button to select a folder and save the folder name in the BackupFolder field. I also added the option to select the type of file name: Same name as Database name and Use specific name.
Form View of Backup Form:
So, what is the best way to arrive at a true pain portrait of this individual? Medical history-taking often is unstable, according to psychiatrist Arthur Barsky, MD.1 “Patients oftentimes fail to recall (and therefore under-report) the incidence of previous symptoms and events, tend to combine separate, similar occurrences into a single generic memory, and falsely recall medical events and symptoms that did in fact. cheap viagra Most pharmacies want you to jump through rings of fire and charge a ton of money for their health. sildenafil cheapest Reactions: The most widely recognized symptoms with purchase cialis are cerebral pain, flushing, stuffy or runny nose, acid reflux, annoyed stomach, tipsiness or back torment. It was the first drugs introduced cheap cialis soft for treatment of male impotence.
Select Folder:
After we create a button next to the BackupFolder textbox, we need to add the VB code under the On Click Event Procedure of the button.
You can create a Sub procedure or function then call it under the On Click Event Procedure of the select folder button on the picture above. The VB code for selecting the folder name is provided below.
More details on SelectFolder() or get code at https://www.iaccessworld.com/folder-get-folder-get-path/
Select Option for Filename:
Since you have two options to add two types of filename, you need to create the option group for these two options: Same name as database name and Use specific name. We set the Visible Property of the BackupFileName texbox to False, so it will be invisible when form is loaded.
Now we insert the code under the On Got Focus Event Procedure of the 1st option (Same name as database name) to hide the BackupFileName text box. See code below:
Private Sub Option19_GotFocus()Me.BackupFileName.Visible = FalseEnd Sub
The we insert the code under the On Got Focus Event Procedure of the 2nd option (Use specific name) to show the BackupFileName text box. So we can change or type in our specific filename. See code below:
Private Sub Option21_GotFocus()Me.BackupFileName.Visible = TrueEnd Sub
Create Function for Setting Backup
After we completed the design of this form then we need to add the VB code under the On Click Event Procedure of the Setting Backup button. We can create a function and then call it under this On Click Event Procedure. The purpose of this function is to make sure the user have the backup folder and select or enter filename in the setting form. See code below:
VB Code:
Private Sub Command0_Click()Call SettingBackup 'call functionEnd Sub
Function SettingBackup() As Boolean 'Full function or Sub ProcedureDim FileName As StringDim objFSO As Object
If IsNull(Me.BackupFolder) Or Me.BackupFolder = "" ThenMsgBox "Please select folder", vbInformation, " Backup Location or Folder is needed"Me.BackupFolder.SetFocusElseSet objFSO = CreateObject("scripting.FileSystemObject")If Me.FileNameOption = 1 Then ' Select option: same name as database nameFileName = objFSO.GetFileName(CurrentDb.Name) ' return filename with extensionFileName = Mid(FileName, 1, InStr(FileName, ".") - 1) ' return filename only' MsgBox (FileName)Me.BackupFileName = FileNameElse‘Select option: Use specific name – users must enter the filename in the BackupFilename textbox‘Filename is requiredIf IsNull(Me.BackupFileName) Or Me.BackupFileName = "" ThenMsgBox "Please enter a file name", vbInformation, " Filename is needed"Me.BackupFileName.SetFocusExit FunctionEnd IfEnd IfSet objFSO = NothingDoCmd.Close ‘close the Setting Backup formEnd IfEnd Function
The Scripting.FileSystemObject is used to gain an access to a computer’s file system. It can create new files, folders, directory paths, and access existing ones. The FileSystemObject has many usable methods such as CopyFile, DeleteFile, CopyFolder etc.
More information about FileSystemObject here.
Create Backup Function in Module
We need to create the function in the Module in order to call the function anywhere from the current database. In order to let the program to create auto-backup, we can call the function when the main form is loaded or closed. I have created two functions below which work in different method.
Methods of Backup:
- Backup database file (copy file)
- Backup table only (copy table only)
Backup Current Database to New File (Copy File)
The code below is to copy the current Access file to the new location with a specified folder name, file name, and certain day from the information on the setting table. This type of backup will save everything such as tables, forms, queries, reports, and modules from the current database to new database. The user also can set the backup to override the old file every week or keep every backup file.
Full Code:
Public Function CreateBackupV2() As BooleanDim Source As StringDim Path, Target, FileFormat, FileName, TodayDay As StringDim objFSO As ObjectDim rst As RecordsetDim db As DatabaseDim fld As FieldSource = CurrentDb.NameSet db = CurrentDbSet rst = db.OpenRecordset("select * from Setbackup where backupID = 1")With rstIf !Override.Value = True ThenFileFormat = Format(Now(), "_ddd") 'return "_Sun"ElseFileFormat = Format(Now(), "_mm-dd-yyyy") 'return "_12-20-2020"End IfPath = !BackupFolder.ValueFileName = !BackupFileName.ValueTarget = Path & "\" & FileName & FileFormat & ".accdb"TodayDay = Format(Now(), "dddd")'MsgBox (TodayDay)For Each fld In .FieldsIf fld.Name = TodayDay And fld.Value = True ThenSet objFSO = CreateObject("Scripting.FileSystemObject")If objFSO.folderExists(Path) Thena = objFSO.CopyFile(Source, Target, True 'Copy current file to new locationElseobjFSO.Createfolder (Path) ‘create folder with path if not existsa = objFSO.CopyFile(Source, Target, True) 'Copy current file to new locationEnd IfEnd IfNext fldSet objFSO = NothingSet a = NothingEnd WithSet db = NothingSet rst = NothingEnd Function
Call Backup Function When File Opens
You will call for the CreateBackup() function under the Fom_Load() procedure, as shown below.
Private Sub Form_Load()Call CreateBackupV2End Sub
Call Backup Function When File Closed
You can call the CreateBackup() function under the Fom_Close() procedure as shown below.
Private Sub Form_Close()Call CreateBackupV2End Sub
Example of the 1st method (Copy file)
This picture below is showing all Access objects on the original file which include Tables, Forms, …, etc. This database also has one linked table (tbl_Customer) which is linked to another database file. The backup file is created after the main form is loaded. The new file also has same all Access objects as the original file. All objects are copied to the new file.
Backup Only Table or Data to New File (Copy Table Only)
The setting for this backup is same way as the function above. However, this method will back up only the data. It will save only tables from the current database to new database. The other objects such as forms, reports, or modules will not be saved in the new backup database. By this way, it will need less space for saving backup.
Full Code:
Public Function CreateBackupV3() As Boolean
Dim Target, FileFormat, FileName, TodayDay As String
Dim objFSO As Object
Dim Path As String
Dim rst As Recordset
Dim db As Database
Dim fld As Field
Dim oTB As TableDef
Set db = CurrentDb
Set rst = db.OpenRecordset("select * from Setbackup where backupID = 1")
With rst
If !Override.Value = True Then
FileFormat = Format(Now(), "_ddd") 'return "_Sun"
Else
FileFormat = Format(Now(), "_mm-dd-yyyy hh-mm AM/PM") 'return "_12-20-2020 02-08 PM"
End If
Path = !BackupFolder.Value
FileName = !BackupFileName.Value
Target = Path & "\" & FileName & FileFormat & ".accdb" ' New Access file name with all paths
TodayDay = Format(Now(), "dddd")
'MsgBox (TodayDay)
For Each fld In .Fields
If fld.Name = TodayDay And fld.Value = True Then
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(Target) Then
Kill Target 'Delete the existing file if any
End If
' Create new Access file with general language like English, German, French, Spanish
DBEngine.CreateDatabase Target, dbLangGeneral
With db
For Each oTB In .TableDefs
Select Case True
Case Left(oTB.Name, 1) = "~"
Case Left(oTB.Name, 4) = "msys"
Case Else
'Copy tables from current file to new Access file
.Execute "select * into [" & Target & "].[" & oTB.Name & "] from [" & oTB.Name & "]"
End Select
Next
‘ MsgBox "Backup successfully"
End With
End If
Next fld
Set objFSO = Nothing
Set a = Nothing
End With
Set db = Nothing
Set rst = Nothing
End Function
Example of the 2nd method (Copy Table Only)
This picture below is showing all Access objects on the original file which include Tables, Forms, …, etc. This database also has the tbl_Customer table which is linked to another database file.
|
This picture below is showing all Access objects on the original file which include Tables, Forms, …, etc. This database is a front-end database that was created from the split database method. All tables are linked to the external database.
 |
This picture below is the object listed on the backup database which is created from the function CreatedBackupV3() above for the 2nd method (Backup Table Only). There are only tables on the Navigation Pane. It backs up only data, no matter it is local data or linked data from another database.
Time Format for Filename
I will illustrate more examples of changing the time format that I added it at the end of file name in the VB code.
For example, if:
Now() = Saturday August 8, 2020 12:15 PM
File name = myBackup
Path = C:\TestDB
Target = Path & “\” & FileName & FileFormat & “.accdb”
VBA Code: FileFormat = Format(Now(), “_ddd”)
Result: Target = C:\TestDB \myBackup_Sun.accdb
VBA Code: FileFormat = Format(Now(), “_mm”)
Result: Target = C:\TestDB\myBackup _08.accdb
VBA Code: FileFormat = Format(Now(), “_mmm-ddd”)
Result: Target = C:\TestDB\myBackup _Aug-Sat.accdb
VBA Code: FileFormat = Format(Now(), “_ddd hh-mm”)
Result: Target = C:\TestDB\myBackup _Sat 12-15.accdb
VBA Code: FileFormat = Format(Now(), “_mm-dd-yy hh-mm AM/PM”)
Result: Target = C:\TestDB\myBackup_08-08-20 12-15 PM.accdb
